package impl;

import bean.Member;
import dao.MemberDao;
import util.DBHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @classname:MemberDaoImpl
 * @description:
 * @author: Mr.Z
 * @date: 2020/12/31 17:15
 * @version: 0.1
 * @since:1.8
 */
public class MemberDaoImpl implements MemberDao {
    private Connection conn;
    private String sql;
    private PreparedStatement ps;
    private ResultSet rs;
    private int result;

    //添加新的用户
    @Override
    public int addMember(Member member) {
        conn = DBHelper.getCon();
        sql = "insert into member (mname,pass,image,contact) values (?,?,?,?)";
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, member.getMname());
            ps.setString(2, member.getPass());
            ps.setString(3, member.getImage());
            ps.setString(4, member.getContact());
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps);
        }
        return result;
    }

    //根据Id删除会员信息
    @Override
    public int deleteMemberById(int mid) {
        conn = DBHelper.getCon();
        sql= "delete from member where mid =?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,mid);
            result =ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBHelper.closeResources(conn, ps);
        }
        return result;
    }

    //更新用户信息通过id
    @Override
    public int updateMemberById(Member member,int mid) {
        conn = DBHelper.getCon();
        sql= "update member set mname=?,pass=?,image=?,contact=?,points = ?,balance=balance+? where mid = ?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1,member.getMname());
            ps.setString(2,member.getPass());
            ps.setString(3,member.getImage());
            ps.setString(4,member.getContact());
            ps.setObject(5,member.getPoints());
            ps.setObject(6,member.getBalance());
            ps.setInt(7,mid);
            result =ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBHelper.closeResources(conn, ps);
        }
        return result;
    }


    //查询所有会员信息
    @Override
    public List<Member> selectAll() {
        conn = DBHelper.getCon();
        sql="select * from member";
        List<Member> list = new ArrayList<>(10);
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                list.add(new Member(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps,rs);
        }
        return list;
    }

    //查询用户通过Id
    @Override
    public Member selectMemberById(int mid) {
        conn = DBHelper.getCon();
        sql="select * from member where mid= ?";
        Member member = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,mid);
            rs = ps.executeQuery();
            if (rs.next()){
               member = new Member(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn,ps,rs);
        }
        return member;
    }

    //修改用户余额
    @Override
    public int updateMemberBalance(int mid,int money) {
        conn = DBHelper.getCon();
        sql="update member set balance=balance+? where mid = ?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,money);
            ps.setInt(2,mid);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.closeResources(conn, ps);
        }
        return result;
    }
}
